package db.day04;

import java.sql.*;
import java.util.Random;

/**
 * @Author Gan
 * @Date 2021/5/25 19:17
 * @Description: 采用两种不同的批处理方式插入10000条数据，比较速度差别，
 * 然后去根据主键查询和根据普通字段查询一条数据，比较查询速度差别
 */
public class Day04Work {
    public static void main(String[] args) throws SQLException {
        long startTime = System.currentTimeMillis();
        // 使用statement批处理
        statementInsert();
        long endTime1 = System.currentTimeMillis();

        // 使用prepareStatement批处理
        prepareStatementInsert();
        long endTime2 = System.currentTimeMillis();

        //根据主键查询一条数据
        StatementQuery1();
        long endTime3 = System.currentTimeMillis();

        //根据普通字段查询一条数据
        StatementQuery2();
        long endTime4 = System.currentTimeMillis();

        System.out.println("根据普通字段查询一条数据所耗时是：" + (endTime4 - endTime3) + "毫秒");
        System.out.println("根据主键查询一条数据所耗时是：" + (endTime3 - endTime2) + "毫秒");
        System.out.println("使用statement批处理耗时：" + (endTime1 - startTime) + "毫秒。");
        System.out.println("使用prepareStatement批处理耗时：" + (endTime2 - endTime1) + "毫秒。");
    }

    private static void StatementQuery2() throws SQLException {
        //加载驱动
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        //获得链接对象
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/GraduateSchool?characterEncoding=utf8",
                "root", "123456");
        Statement statement = conn.createStatement();
        for (int i = 0; i < 10000; i++) {

            statement.executeQuery("select tno from tutor where position = '讲师'");

        }

        statement.close();
        conn.close();

    }

    private static void StatementQuery1() throws SQLException {
        //加载驱动
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        //获得链接对象
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/GraduateSchool?characterEncoding=utf8",
                "root", "123456");
        Statement statement = conn.createStatement();
        for (int i = 0; i < 10000; i++) {

            statement.executeQuery("select tname from tutor where tno =" + i);

        }

        statement.close();
        conn.close();

    }

    private static void prepareStatementInsert() throws SQLException {
        //加载驱动
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        //获得链接对象
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/GraduateSchool?characterEncoding=utf8" +
                "&rewriteBatchedStatements=true", "root", "123456");
        PreparedStatement preparedStatement =
                conn.prepareStatement("insert into tutor values (?,?,?,?)");
        int tNo;
        String tName;
        String position;
        String researchDirection;
        //插入1w条数据
        for (int i = 10000; i < 20000; i++) {
            tNo = i;
            tName = "导师" + i;
            position = randomPosition();
            researchDirection = randomResearchDirection();
            preparedStatement.setInt(1, tNo);
            preparedStatement.setString(2, tName);
            preparedStatement.setString(3, position);
            preparedStatement.setString(4, researchDirection);
            //预编译之后进行批处理
            preparedStatement.addBatch();
        }
        //批量进行处理
        preparedStatement.executeBatch();
        //释放资源
        preparedStatement.close();
        conn.close();

    }

    //随机分配一个研究方向
    private static String randomResearchDirection() {
        String[] arr = new String[]{"嵌入式", "大数据", "网络安全"};
        int i = new Random().nextInt(3);
        return arr[i];

    }

    //随机分配一个职位
    private static String randomPosition() {
        String[] arr = new String[]{"讲师", "副教授", "教授"};
        int i = new Random().nextInt(3);
        return arr[i];
    }

    private static void statementInsert() throws SQLException {
        //加载驱动
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        //获得链接对象
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/GraduateSchool" +
                        "?characterEncoding=utf8&rewriteBatchedStatements=true",
                "root", "123456");
        Statement statement = conn.createStatement();
        int tNo;
        String tName;
        String position;
        String researchDirection;
        String sql;
        for (int i = 0; i < 10000; i++) {
            tNo = i;
            tName = "导师" + i;
            position = randomPosition();
            researchDirection = randomResearchDirection();
            sql = "insert into tutor values(" + tNo + ",'" + tName + "','" + position + "','" + researchDirection + "')";
            statement.addBatch(sql);
        }
        statement.executeBatch();
        statement.close();
        conn.close();
    }
}
